package cn.edu.scau.cmi.maikejian.comprehension.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.edu.scau.cmi.maikejian.comprehension.domain.Student;
import cn.edu.scau.cmi.maikejian.comprehension.domain.Teacher;



public class StudentDAO{
	
	static Connection conn;
	public StudentDAO(Connection conn) {
		this.conn=conn;
	}
	
	public static int newStudent(Student student) {

	    int i = 0;
	    String sql = "insert into student (id,name) values(?,?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(2, student.getName());
	        pstmt.setLong(1, student.getId());
	        i = pstmt.executeUpdate();
	        pstmt.close();
	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public static int update_name(Student student) {

	    int i = 0;
	    String sql = "update student set name='" + student.getName() + "' where id='" + student.getId() + "'";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        pstmt.close();
	        //conn.close();
	    } catch (SQLException e) {
	    	System.out.println("修改失败！！！");
	        //e.printStackTrace();
	    }
	    return i;
	}
	
	public static int update_tutor(Student student) {
		int i = 0;
		 String sql;
		if(student.getTeacher()==null) {
			sql = "update student set tutor='null' where Name='" + student.getId() + "'";
			System.out.println("没有老师");
		}
		else {
			sql = "update student set tutor='" + student.getTeacher().getId() + "' where id='" + student.getId() + "'";
			System.out.println("有老师");
		}
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        pstmt.close();
	        //conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public static Student getStudnet(long id) {
		Student stu=null;
		String sql = "select * from student where id='"+id+"'";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement)conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
	        int col = rs.getMetaData().getColumnCount();
	        System.out.println();
	        if(rs.next()) {
		        long stu_id = rs.getLong(1);
		        //System.out.println(stu_id);
		        String name = rs.getString(2);
		        //System.out.println(name);
		 
		        long tutor = rs.getLong(3);
		        //System.out.println(tutor);
		        Teacher tea = TeacherDAO.getTeacher(tutor);
		        
		        stu = new Student(stu_id,name,tea);
	        }
	        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("出错了");
			e.printStackTrace();
		}
		
		return stu;
	}
	
	public static Integer listTeacherINTRstudents(long id) {

	    String sql = "select * from student where tutor='"+id+"'";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        int col = rs.getMetaData().getColumnCount();
	        System.out.println("============================");
	        while (rs.next()) {
	            for (int i = 1; i <= col; i++) {
	                System.out.print(rs.getString(i) + "\t");
	                if ((i == 2) && (rs.getString(i).length() < 8)) {
	                    System.out.print("\t");
	                }
	             }
	            System.out.println("");
	        }
	            System.out.println("============================");
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return null;
	}
	
	public static Integer listAllstudents() {

	    String sql = "select * from student";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        int col = rs.getMetaData().getColumnCount();
	        System.out.println("============================");
	        while (rs.next()) {
	            for (int i = 1; i <= col; i++) {
	                System.out.print(rs.getString(i) + "\t");
	                if ((i == 2) && (rs.getString(i).length() < 8)) {
	                    System.out.print("\t");
	                }
	             }
	            System.out.println("");
	        }
	            System.out.println("============================");
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return null;
	}
	
	public static int delete(Long id) {

	    int i = 0;
	    String sql = "delete from student where id='" + id + "'";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        pstmt.close();
	        //conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}

}